const express = require('express')
const cors = require('cors')
const sqlite3 = require('sqlite3').verbose()
const path = require('path')

const app = express()
const PORT = process.env.PORT || 3001

// 中间件
app.use(cors())
app.use(express.json())
app.use(express.urlencoded({ extended: true }))
app.use('/uploads', express.static(path.join(__dirname, 'uploads')))

// 初始化数据库
const db = new sqlite3.Database('./forum.db')

// 创建表
db.serialize(() => {
  // 用户表
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    avatar TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`)

  // 版块表
  db.run(`CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    color TEXT DEFAULT '#3b82f6',
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`)

  // 帖子表
  db.run(`CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    views INTEGER DEFAULT 0,
    likes INTEGER DEFAULT 0,
    is_pinned BOOLEAN DEFAULT 0,
    is_locked BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users (id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
  )`)

  // 回复表
  db.run(`CREATE TABLE IF NOT EXISTS replies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    parent_id INTEGER,
    likes INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users (id),
    FOREIGN KEY (post_id) REFERENCES posts (id),
    FOREIGN KEY (parent_id) REFERENCES replies (id)
  )`)

  // 点赞表
  db.run(`CREATE TABLE IF NOT EXISTS likes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    target_type TEXT NOT NULL,
    target_id INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    UNIQUE(user_id, target_type, target_id)
  )`)

  // 插入默认版块
  const categories = [
    { name: '主机游戏', description: 'PlayStation、Xbox、Switch等主机游戏讨论', color: '#ef4444' },
    { name: 'PC游戏', description: 'Steam、Epic等PC平台游戏讨论', color: '#3b82f6' },
    { name: '手游', description: '手机游戏讨论专区', color: '#10b981' },
    { name: '独立游戏', description: '独立游戏开发者与玩家交流', color: '#f59e0b' }
  ]

  categories.forEach(category => {
    db.run(`INSERT OR IGNORE INTO categories (name, description, color) VALUES (?, ?, ?)`, 
      [category.name, category.description, category.color])
  })
})

// 路由
app.get('/api/health', (req, res) => {
  res.json({ status: 'ok', message: 'Server is running' })
})

// 获取版块列表
app.get('/api/categories', (req, res) => {
  db.all('SELECT * FROM categories ORDER BY sort_order', (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else {
      res.json(rows)
    }
  })
})

// 获取帖子列表
app.get('/api/posts', (req, res) => {
  const { category, sort = 'created_at', order = 'DESC', limit = 20, offset = 0 } = req.query
  
  let query = `
    SELECT 
      p.*,
      u.username as author_username,
      u.avatar as author_avatar,
      c.name as category_name,
      c.color as category_color,
      COUNT(r.id) as reply_count
    FROM posts p
    JOIN users u ON p.author_id = u.id
    JOIN categories c ON p.category_id = c.id
    LEFT JOIN replies r ON p.id = r.post_id
  `
  
  const params = []
  
  if (category) {
    query += ' WHERE p.category_id = ?'
    params.push(category)
  }
  
  query += ` GROUP BY p.id ORDER BY p.is_pinned DESC, ${sort} ${order} LIMIT ? OFFSET ?`
  params.push(limit, offset)
  
  db.all(query, params, (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else {
      res.json(rows)
    }
  })
})

// 获取单个帖子
app.get('/api/posts/:id', (req, res) => {
  const postId = req.params.id
  
  const query = `
    SELECT 
      p.*,
      u.username as author_username,
      u.avatar as author_avatar,
      c.name as category_name,
      c.color as category_color
    FROM posts p
    JOIN users u ON p.author_id = u.id
    JOIN categories c ON p.category_id = c.id
    WHERE p.id = ?
  `
  
  db.get(query, [postId], (err, row) => {
    if (err) {
      res.status(500).json({ error: err.message })
    } else if (!row) {
      res.status(404).json({ error: 'Post not found' })
    } else {
      // 增加浏览量
      db.run('UPDATE posts SET views = views + 1 WHERE id = ?', [postId])
      res.json(row)
    }
  })
})

// 启动服务器
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`)
})
